﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL.PreAccept
{
    public class RequestEntity
    {
        public Int32 R_Id { get; set; }
        public string Loan_Type { get; set; }
        public double Loan_Value { get; set; }
        public double MonthlyPayment { get; set; }
        public Int32 NomberOfMonths { get; set; }
        public Int32 Client_Id { get; set; }

    }

    public class Request
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string ConnString = ConfigurationManager.ConnectionStrings["BankElKheir"].ToString();
        private static string CmdText = "";

        public static bool Add(RequestEntity s)
        {
            bool flag = true;

            CmdText = "Insert into Request (R_Id,Loan_Type,Loan_Value,MonthlyPayment,NomberOfMonths,Client_Id) values "+
                "(@R_Id,@Loan_Type,@Loan_Value,@MonthlyPayment,@NomberOfMonths,@Client_Id)";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@R_Id", s.R_Id);
            cmd.Parameters.AddWithValue("@Loan_Type", s.Loan_Type);
            cmd.Parameters.AddWithValue("@Loan_Value", s.Loan_Value);
            cmd.Parameters.AddWithValue("@MonthlyPayment", s.MonthlyPayment);
            cmd.Parameters.AddWithValue("@NomberOfMonths", s.NomberOfMonths);
            cmd.Parameters.AddWithValue("@Client_Id", s.Client_Id);
            

            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num == 0)
                    flag = false;
            }

            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى ادخال البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;


        }

        public static bool Update(RequestEntity s)
        {
            bool flag = true;//R_Id Loan_Type Loan_Value MonthlyPayment NomberOfMonths Client_Id

            CmdText = "Update Request set Loan_Type = @Loan_Type, Loan_Value=@Loan_Value, MonthlyPayment=@MonthlyPayment, NomberOfMonths =@NomberOfMonths,Client_Id=@Client_Id WHERE R_Id= @R_Id ";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);


            cmd.Parameters.AddWithValue("@Loan_Type", s.Loan_Type);
            cmd.Parameters.AddWithValue("@Loan_Value", s.Loan_Value);
            cmd.Parameters.AddWithValue("@MonthlyPayment", s.MonthlyPayment);
            cmd.Parameters.AddWithValue("@NomberOfMonths", s.NomberOfMonths);
            cmd.Parameters.AddWithValue("@Client_Id", s.Client_Id);
            cmd.Parameters.AddWithValue("@R_Id", s.R_Id);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;

        }

        public static bool Delete(Int32 s)
        {
            bool flag = true;

            CmdText = "Delete from Request where R_Id =@R_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@R_Id", s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ اثناء الحذف ", "رساله ادارية " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;
        }


        private static RequestEntity FillEntityFromReader(SqlDataReader reader)
        {
            RequestEntity x = new RequestEntity();

           
            x.R_Id = Int32.Parse(reader["R_Id"].ToString());
            if (reader["Loan_Type"] != null)
                x.Loan_Type = reader["Loan_Type"].ToString();
            if (reader["Loan_Value"] != null)
                x.Loan_Value = Convert.ToDouble(reader["Loan_Value"].ToString());
            if ((reader["MonthlyPayment"] != null))
                x.MonthlyPayment = Convert.ToDouble(reader["MonthlyPayment"].ToString());
            if (reader["NomberOfMonths"] != null)
                x.NomberOfMonths = Convert.ToInt32(reader["NomberOfMonths"].ToString());
            x.Client_Id = Convert.ToInt32(reader["Client_Id"].ToString());



            return x;
        }

        public static List<RequestEntity> GetRequestByClientID(Int32 id)
        {
            RequestEntity x;
            List<RequestEntity> R_List = new List<RequestEntity>();

            CmdText = "Select * from Request where Client_Id = @Client_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Client_Id", id);

            try
            {
                Con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    R_List.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return R_List;
        }

        public static List<RequestEntity> GetRequestByID(Int32 id)
        {
            RequestEntity x;
            List<RequestEntity> R_List = new List<RequestEntity>();

            CmdText = "Select * from Request where R_Id = @CR_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@CR_Id", id);

            try
            {
                Con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    R_List.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return R_List;
        }

    }
}
